﻿using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Internal;
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;
using MySqlConnector;
using MyStyle.Model;
using MyStyle.RepositoryLayer;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;

namespace MyStyle.Repositry
{
    public class Repository<T> : IRepository<T> where T : BaseEntity
    {

        private readonly ApplicationDbContext _applicationDbContext;
        private DbSet<T> entities;

        public Repository(ApplicationDbContext applicationDbContext)
        {
            _applicationDbContext = applicationDbContext;
            entities = _applicationDbContext.Set<T>();
        }

        public void Delete(T entity)
        {
            if (entity == null)
            {
                throw new ArgumentNullException("entity");
            }
            entities.Remove(entity);


            _applicationDbContext.SaveChanges();
        }
        public T Get(int Id)
        {
            // entities.ToList();
            // return entities.SingleOrDefault(c => c.Id == Id);
            //获取全部数据
            //var blogs = context.Blogs.ToList();

            ////获取单个实体
            //var blog = context.Blogs.Single(b => b.BlogId == 1);

            ////筛选
            //var blogs = context.Blogs
            //    .Where(b => b.Url.Contains("dotnet"))
            //    .ToList();
            return entities.Where(c => c.Id.Equals(Id)).FirstOrDefault();
        }
        public IEnumerable<T> GetAll()
        {
            return entities.AsEnumerable();
        }

        public object getRe()
        {

            // entities.Include(e => )
            //return entities.FromSqlRaw("select * from customer where id={0} and is_active = {1}","1","1").ToList();
            //return entities.FromSqlRaw("select * from customer where id={0} and is_active = {1}", "1", "1").ToList();
            // entities.FromSqlRaw().Where


            //var data = _dbContext.Users.Join(_dbContext.Roles, u => u.RoleId, r => r.Id, (u, r) => new { u, r })
            //    .Where(t => t.u.UserName == username && t.u.PassWord == password)
            //    .Where(t => t.u.UserSite.All(x => x.UserId == t.u.Id))
            //    .Where(t => t.r.RoleMenus.All(x => x.RoleId == t.r.Id))
            //    .Select(t => new UserDto()
            //    {
            //        Id = t.u.Id,
            //        UserName = t.u.UserName,
            //        PassWord = t.u.PassWord,
            //        RealName = t.u.RealName,
            //        Phone = t.u.Phone,
            //        RoleId = t.u.RoleId,
            //        RoleName = t.r.RoleName,
            //        LastIp = t.u.LastIp,
            //        LastTime = t.u.LastTime,
            //        Menus = t.r.RoleMenus.Select(x => (int)x.MenuId).ToList(),
            //        Sites = t.u.UserSite.Select(x => x.SiteId).ToList()
            //    });

            //     public int Id { get; set; }
            //public int CustomerId { get; set; }
            //public string CustomerName { get; set; }
            //public string CustomerItemName { get; set; }
            //public string PurchasesProduct { get; set; }

            // 经典left join
            //IList list = _applicationDbContext.Customer.LeftJoin(_applicationDbContext.CustomItem, c => c.Id, ci => ci.CustomerId, (c, ci) => new { c, ci })
            //        .Where(t => t.c.Id == 1)
            //        .Select(t => new CustomerDto()
            //        {
            //            Id = t.c.Id,
            //            CustomerId = t.ci.CustomerId,
            //            CustomerName = t.c.CustomerName,
            //            CustomerItemName = t.ci.CustomItemName,
            //            PurchasesProduct = t.c.PurchasesProduct
            //        })
            //        .ToList();

            // 需外键关联版本
            //IList list = _applicationDbContext.Customer.Include(x => x.CustomItem)
            //     //     //.Where(c => )
            //     //     .AsNoTracking()
            //     .AsSingleQuery()
            //     .ToList();

            // 原生SQL
            //IList list = _applicationDbContext.Customer.FromSqlRaw("select c.*,ci.* from customer c left join customer_item ci on ci.cid = c.id where c.id = {0}", "1")
            //    .ToList();

            // 高级查询
            var query3 = from c in _applicationDbContext.Customer
                        join ci in _applicationDbContext.CustomItem on c.Id equals ci.CustomerId
                        select new { ci.CustomerId, ci.CustomItemName, ci.Id, c.CustomerName };

            // where条件连表查询 带.DefaultIfEmpty()就是left join，否则是join
            var query2 = from c in _applicationDbContext.Customer
                        from ci in _applicationDbContext.CustomItem
                        .Where(ci => ci.CustomerId == c.Id)
                        select new { ci.CustomerId, ci.CustomItemName, ci.Id, c.CustomerName };

            IList list2 = query2.ToList();

            // 查询COUNT
            var query1 = from c in _applicationDbContext.Customer
                        from ci in _applicationDbContext.CustomItem
                        .Where(ci => ci.CustomerId == c.Id)
                        group ci by ci.CustomerId into g
                        select new { g.Key, Count = g.Count() };
            IList list1 = query1.ToList();

            // 连表查询left join
            //var query0 = from c in _applicationDbContext.Customer
            //            join ci in _applicationDbContext.CustomItem on c.Id equals ci.CustomerId into leftjoin
            //            from ci in leftjoin.DefaultIfEmpty()
            //            select new { ci.CustomerId, ci.CustomItemName, ci.Id, c.CustomerName };
            //IList list0 = query0.ToList();

            // 分页查询
            var lastDate = new DateTime(2020, 1, 1);
            var nextPage = _applicationDbContext.Customer
                .OrderBy(b => b.Id)
                .ThenBy(b => b.CustomerName)
                .Where(b => b.CreateDate > lastDate && b.CreateDate < DateTime.Now)
                // 相当于currentPage，当前第几页
                .Skip(4)
                // 相当于pageSize，每页多少条
                .Take(2);
            IList list = nextPage.ToList();
            int total = nextPage.Count();

            IOrderedQueryable<Customer> customers = _applicationDbContext.Customer.Include(p => p.CustomItem).OrderByDescending(p => p.CreateDate);

            var query10 = customers.First();


            return query10;
        }

        public void Insert(T entity)
        {
            if (entity == null)
            {
                throw new ArgumentNullException("entity");
            }
            entities.Add(entity);
            _applicationDbContext.SaveChanges();
        }
        public void Remove(T entity)
        {
            if (entity == null)
            {
                throw new ArgumentNullException("entity");
            }
            entities.Remove(entity);
        }
        public void SaveChanges()
        {
            //将操作同步到数据库中
            _applicationDbContext.SaveChanges();
        }
        public void Update(T entity)
        {
            if (entity == null)
            {
                throw new ArgumentNullException("entity");
            }
            entities.Update(entity);
            _applicationDbContext.SaveChanges();
        }

    }

    internal class CustomerDto
    {
        public int Id { get; set; }
        public int CustomerId { get; set; }
        public string CustomerName { get; set; }
        public string CustomerItemName { get; set; }
        public string PurchasesProduct { get; set; }

        public CustomerDto()
        {
        }
    }
}
